package com.qianfeng.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.qianfeng.commons.DBManager;
import com.qianfeng.commons.DBUtils;
import com.qianfeng.dao.IUserDao;

import com.qianfeng.entity.User;

public class UserDaoImpl extends DBUtils<User> implements IUserDao {

	@Override
	public int checkNameExits(String username) {
		String sql = "SELECT count(*) from t_user where user_name = ?";
		return super.getCount(sql, username);
	}

	@Override
	public int addUser(User user) {
		String sql = "insert into t_user(user_name,pwd)values(?,?)";
		return super.commonOper(sql, user.getUser_name(), user.getPwd());
	}

	@Override
	public int update(User u) {
		String sql = "update t_user set nick_name = ?,sex=?,birthday=?,phone= ?,codes=?,email=?,address=?,isadmin=?,regist_date=?,lockstate=? where user_name = ?";
		return super.commonOper(sql, u.getNick_name(), u.getSex(), u.getBirthday(), u.getPhone(), u.getCodes(),
				u.getEmail(), u.getAddress(), "否", new Date(), 0, u.getUser_name());
	}

	@Override
	public User checkLogin(User user) {
		String sql = "SELECT * from t_user where user_name = ? and pwd = ?";
		return super.getSingleInstance(sql, User.class, user.getUser_name(),user.getPwd());
	}

	@Override
	public User checkBackLogin(String username, int password) {
		String sql = "select * from t_user where user_name = ? and pwd = ? and isadmin='是'";
		return getSingleInstance(sql, User.class, username, password);
	}

	@Override
	public int querycount() {
		String sql="SELECT count(*) from t_user";
		int count=0;
		Connection connection = DBManager.getConnection();
		try {
			Statement st = connection.createStatement();
			
			ResultSet rs = st.executeQuery(sql);
			while(rs.next())
			count =rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return count;
	}

	@Override
	public List<User> queryfenye(int rowIndex, int pageSize) {
		List<User> list=new ArrayList<User>();
		String sql="SELECT * from t_user limit ?,?";
		Connection connection = DBManager.getConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setObject(1, rowIndex);
			ps.setObject(2, pageSize);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt(1));
				user.setUser_name(rs.getString(2));
				user.setNick_name(rs.getString(3));
				user.setPwd(rs.getInt(4));
				user.setSex(rs.getString(5));
				user.setBirthday(rs.getDate(6));
				user.setPhone(rs.getString(7));
				user.setEmail(rs.getString(8));
				user.setRegist_date(rs.getDate(9));
				user.setAddress(rs.getString(10));
				user.setCodes(rs.getString(11));
				user.setIsadmin(rs.getString(12));
				user.setLockstate(rs.getInt(13));
				list.add(user);
				
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public User getUserById(int userid) {
		String sql = "select * from t_user where id = ?";
		return getSingleInstance(sql, User.class, userid);
	}

	@Override
	public int updateUserInfo(User u) {
		String sql = "update t_user set user_name = ?,nick_name = ?,pwd = ?,sex = ?,birthday = ?,phone = ?,email = ?,regist_date=?,address=?,codes = ?,isadmin = ? where id = ?";
		return commonOper(sql, u.getUser_name(), u.getNick_name(), u.getPwd(), u.getSex(), u.getBirthday(),
				u.getPhone(), u.getEmail(), u.getRegist_date(), u.getAddress(), u.getCodes(),u.getIsadmin(),u.getId());
	}

	@Override
	public int updateUserLockState(int userid, int lockstate) {
		String sql = "update t_user set lockstate = ? where id = ?";
		return commonOper(sql, lockstate, userid);
	}

	@Override
	public int deleteByid(int id) {
		String sql = "delete from t_user where id = ?";
		return commonOper(sql, id);
	}

	@Override
	public int addUserInfo(User u) {
		String sql = "insert into t_user values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
		return commonOper(sql, u.getUser_name(), u.getNick_name(), u.getPwd(), u.getSex(), u.getBirthday(),
				u.getPhone(), u.getEmail(), u.getRegist_date(), u.getAddress(), u.getCodes(), "否", 0);
	}

}
